<?php
/**
 * Created by PhpStorm.
 * User: alei
 * Date: 11/5/2017 AD
 * Time: 17:09
 */
namespace App\Models\Mysql\tonggao;

//use Illuminate\Database\Eloquent\Model;
use App\Models\Traits\BaseDBMethod;
use DB;

class article
{
	use BaseDBMethod;
	//数据库连接配置项，见config/database.php
	const DATABASE_CONF = 'tonggao_mysql';
	//pre_ct_ui 首页轮播图所在表
	const TABLE_NAME = 'article';

	//数据库连接配置项，见config/database.php
	//如果需要用ORM，需要设置此项
//	protected $connection = self::DATABASE_CONF;
	//表名，如果需要用ORM，需要设置此项
//	protected $table = self::TABLE_NAME;

	public static function select($sql)
	{
		return self::getConnObj()->select($sql);
	}

	/**
	 * 服务于接口: "给定某个术语，查询相关统计信息和包含这个术语的所有文稿"
	 * `term_occur_count_in_the_article`:这个术语在这个稿件中出现的次数总和
	 * `term_english_count_in_the_article`: 这个术语在这个稿件中有几种译法
	 * term_occur_count: 这个术语在所有稿件中出现的次数总和
	 * term_english_count: 这个术语在所有稿件中有几种译法
	 * @param $termID
	 * @param $size
	 * @return mixed
	 */
	public static function getDataByTermIDByPage($termID, $size)
	{
		if(0 === $termID) return null;


		$result = self::getDBobj(TRUE)
			->select(
				'article.id'
				,'article.name'
				,DB::raw('count(*) AS term_occur_count_in_the_article')
				,'term_detail.term_id')
			->join('term_detail', 'article.id', '=', 'term_detail.article_id')
			->where('term_detail.term_id', '=', $termID)
			->groupBy('article.id')
			->paginate($size)
			->toArray();

		if(! $result) return null;
		if(! isset($result['data']) || empty($result['data'])) return $result;

		$db = self::getConnObj(TRUE);
		//获取额外的字段.
		//获取term_occur_count; term_english_count
		$termOccurCount = $db->select("SELECT count(*) AS term_occur_count FROM term_detail WHERE term_id=:term_id", ['term_id' => $termID]);
		$result['term_occur_count'] = $termOccurCount[0]->term_occur_count;

		$tmp = $db->select("SELECT english, count(*) AS term_english_count FROM term_detail WHERE term_id=:term_id GROUP BY english", ['term_id'=> $termID]);
		$termEnglishCount = 0;
		foreach($tmp as $v) {
			if(!empty($v->english)) ++$termEnglishCount;
		}
		$result['term_english_count'] = $termEnglishCount;

		array_walk($result['data'], function(&$v) use($db) {
			//分别获取`这个术语在这个稿件中有几种译法`: `term_english_count_in_the_article
			$tmp = $db
				->select("SELECT DISTINCT(`english`) FROM term_detail AS td WHERE td.article_id=:article_id AND td.term_id=:term_id", [
					'article_id' => $v->id,
					'term_id' => $v->term_id
				]);

			$tmpNum = 0;
			if($tmp) {
				foreach($tmp as $v1) {
					if(!empty($v1->english)) ++$tmpNum;
				}
				$v->term_english_count_in_the_article = $tmpNum;
			}
		});


		return $result;
	}
}